package co.fitstart.scheduler.service.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.MapUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.demo2do.core.utils.CollectionUtils;
import com.demo2do.core.utils.DateFormatUtils;
import com.demo2do.core.utils.DateUtils;

import co.fitstart.scheduler.entity.VisitorReport;
import co.fitstart.scheduler.persistence.GenericJdbcSupport;
import co.fitstart.scheduler.service.VisitorReportService;

@Service("visitorReportService")
public class VisitorReportServiceImpl implements VisitorReportService{
    
    @Autowired
    private GenericJdbcSupport genericJdbcSupport;

    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.VisitorReportService#createVisitorReport()
     */
    public void createVisitorReport() {
        
    	Map<String, VisitorReport> experienceAmounts = CollectionUtils.constructMapValueFromList(this.getExperienceAmount(), "siteId");
        Map<String, VisitorReport> coachExperienceAmounts = CollectionUtils.constructMapValueFromList(this.getCoachExperienceAmount(), "siteId");
        Map<String, VisitorReport> orderedAmounts = CollectionUtils.constructMapValueFromList(this.getOrderedAmount(), "siteId");
        
        Date occurDate = DateUtils.parseDate(DateFormatUtils.format(DateUtils.addDays(new Date(), -1), "yyyy-MM-dd"), "yyyy-MM-dd");
        
        for (Map<String, Object> siteIdObject : this.getSite()) {
        	
        	String siteId = siteIdObject.get("id").toString();
            
        	Map<String, Object> parameters = new HashMap<String, Object>();
        	
            parameters.put("experienceAmount", MapUtils.isEmpty(experienceAmounts) ? 
            		                           0 : experienceAmounts.get(siteId) == null ? 
            		                           0 : experienceAmounts.get(siteId).getValue());
            
            parameters.put("coachExperienceAmount", MapUtils.isEmpty(coachExperienceAmounts) ? 
            	                                    0 : coachExperienceAmounts.get(siteId) == null ? 
                                                    0 : coachExperienceAmounts.get(siteId).getValue());
            
            parameters.put("orderedAmount", MapUtils.isEmpty(orderedAmounts) ? 0 : 
            	                            orderedAmounts.get(siteId) == null ? 0 : 
            	                            orderedAmounts.get(siteId).getValue());
            
            parameters.put("occurDate", occurDate);
            parameters.put("siteId", Long.parseLong(siteIdObject.get("id").toString()));
            
            genericJdbcSupport.executeSQL("INSERT INTO `visitor_report` (`visit_amount`,`experience_amount`,`coach_experience_amount`,`ordered_amount`,`occur_date`,`site_id`) VALUES (0,:experienceAmount,:coachExperienceAmount,:orderedAmount,:occurDate,:siteId)", parameters);
        }
        
    }
    
    public void createVisitorReport(Date day) {
        Map<String, VisitorReport> experienceAmounts = CollectionUtils.constructMapValueFromList(this.getExperienceAmount(day), "siteId");
        Map<String, VisitorReport> coachExperienceAmounts = CollectionUtils.constructMapValueFromList(this.getCoachExperienceAmount(day), "siteId");
        Map<String, VisitorReport> orderedAmounts = CollectionUtils.constructMapValueFromList(this.getOrderedAmount(day), "siteId");
        
        Date occurDate = DateUtils.parseDate(DateFormatUtils.format(DateUtils.addDays(day, -1), "yyyy-MM-dd"), "yyyy-MM-dd");
        
        for (Map<String, Object> siteIdObject : this.getSite()) {
            
            String siteId = siteIdObject.get("id").toString();
            
            Map<String, Object> parameters = new HashMap<String, Object>();
            
            parameters.put("experienceAmount", MapUtils.isEmpty(experienceAmounts) ? 
                                               0 : experienceAmounts.get(siteId) == null ? 
                                               0 : experienceAmounts.get(siteId).getValue());
            
            parameters.put("coachExperienceAmount", MapUtils.isEmpty(coachExperienceAmounts) ? 
                                                    0 : coachExperienceAmounts.get(siteId) == null ? 
                                                    0 : coachExperienceAmounts.get(siteId).getValue());
            
            parameters.put("orderedAmount", MapUtils.isEmpty(orderedAmounts) ? 0 : 
                                            orderedAmounts.get(siteId) == null ? 0 : 
                                            orderedAmounts.get(siteId).getValue());
            
            parameters.put("occurDate", occurDate);
            parameters.put("siteId", Long.parseLong(siteIdObject.get("id").toString()));
            
            genericJdbcSupport.executeSQL("INSERT INTO `visitor_report` (`visit_amount`,`experience_amount`,`coach_experience_amount`,`ordered_amount`,`occur_date`,`site_id`) VALUES (0,:experienceAmount,:coachExperienceAmount,:orderedAmount,:occurDate,:siteId)", parameters);
        }
    }
    
    /**
     * 
     * @return
     */
    private List<Map<String, Object>> getSite() {
        return genericJdbcSupport.queryForList("SELECT id FROM site WHERE is_disabled = false ORDER BY id");
    }
    
    /**
     * 
     * @return
     */
    private List<VisitorReport> getExperienceAmount() {
    	 
         String sentence = "SELECT COUNT(schedule.id) as value, "
         		                  + "schedule.site_id as siteId "
         		         + "FROM course_schedule schedule, course course "
         		         + "WHERE schedule.course_id = course.id AND "
         		               + "schedule.is_disabled = false AND "
         		               + "course.is_experience = 1 AND "
         		               + "schedule.signin_time IS NOT NULL AND "
         		               + "schedule.course_type in (1,2,3) AND "
         		               + "schedule.schedule_status = 2 AND "
         		               + "schedule.signin_time BETWEEN :beginDate AND :endDate "
         		         + "GROUP BY schedule.site_id ORDER BY schedule.site_id";
    
         return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(), VisitorReport.class);
    }
    
    private List<VisitorReport> getExperienceAmount(Date day) {
        
        String sentence = "SELECT COUNT(schedule.id) as value, "
                                 + "schedule.site_id as siteId "
                        + "FROM course_schedule schedule, course course "
                        + "WHERE schedule.course_id = course.id AND "
                              + "schedule.is_disabled = false AND "
                              + "course.is_experience = 1 AND "
                              + "schedule.signin_time IS NOT NULL AND "
                              + "schedule.course_type in (1,2,3) AND "
                              + "schedule.schedule_status = 2 AND "
                              + "schedule.signin_time BETWEEN :beginDate AND :endDate "
                        + "GROUP BY schedule.site_id ORDER BY schedule.site_id";
   
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(day), VisitorReport.class);
   }
    
    /**
     * 
     * @return
     */
    private List<VisitorReport> getCoachExperienceAmount() {
    	
         String sentence = "SELECT COUNT(calendar.id) as value, "
         		                  + "relation.site_id as siteId "
         		         + "FROM coach_rest_calendar calendar, principal_report_relation relation "
         		         + "WHERE calendar.coach_id = relation.coach_id AND "
         		               + "relation.coach_id IS NOT NULL AND "
         		               + "relation.manager_id IS NOT NULL AND "
         		               + "relation.site_id IS NOT NULL AND "
         		               + "calendar.is_disabled = false AND "
         		               + "calendar.admin_experience = false AND "
         		               + "calendar.coach_rest_type = 1 AND "
         		               + "calendar.rest_date BETWEEN :beginDate AND :endDate "
         		         + "GROUP BY relation.site_id ORDER BY relation.site_id";
    
         
         return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(), VisitorReport.class);
    }
    
    private List<VisitorReport> getCoachExperienceAmount(Date day) {
        
        String sentence = "SELECT COUNT(calendar.id) as value, "
                                 + "relation.site_id as siteId "
                        + "FROM coach_rest_calendar calendar, principal_report_relation relation "
                        + "WHERE calendar.coach_id = relation.coach_id AND "
                              + "relation.coach_id IS NOT NULL AND "
                              + "relation.manager_id IS NOT NULL AND "
                              + "relation.site_id IS NOT NULL AND "
                              + "calendar.is_disabled = false AND "
                              + "calendar.admin_experience = false AND "
                              + "calendar.coach_rest_type = 1 AND "
                              + "calendar.rest_date BETWEEN :beginDate AND :endDate "
                        + "GROUP BY relation.site_id ORDER BY relation.site_id";
   
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(day), VisitorReport.class);
   }
    
    /**
     * 
     * @return
     */
    private List<VisitorReport> getOrderedAmount() {
    	
         String sentence = "SELECT COUNT(orders.id) as value, "
         		         + "orders.site_id as siteId FROM orders orders "
         		         + "WHERE orders.is_disabled = false AND "
         		               + "orders.order_type in(1,3,4) AND "
         		               + "orders.payment_status = 1 AND "
         		               + "orders.pay_time BETWEEN :beginDate AND :endDate "
         		         + "GROUP BY orders.site_id ORDER BY orders.site_id";
         
         return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(), VisitorReport.class);
    }
    
    private List<VisitorReport> getOrderedAmount(Date day) {
        
        String sentence = "SELECT COUNT(orders.id) as value, "
                        + "orders.site_id as siteId FROM orders orders "
                        + "WHERE orders.is_disabled = false AND "
                              + "orders.order_type in(1,3,4) AND "
                              + "orders.payment_status = 1 AND "
                              + "orders.pay_time BETWEEN :beginDate AND :endDate "
                        + "GROUP BY orders.site_id ORDER BY orders.site_id";
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(day), VisitorReport.class);
   }
    
    /**
     * 
     * @return
     */
    private Map<String, Object> generateYesterdayParams() {
    	
    		Date yesterday = DateUtils.addDays(new Date(), -1);
            Map<String, Object> parameters = new HashMap<String, Object>();
            parameters.put("beginDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
            parameters.put("endDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
            
            return parameters;
    }
    
    private Map<String, Object> generateYesterdayParams(Date day) {
        
        Date yesterday = DateUtils.addDays(day, -1);
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("beginDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return parameters;
}
}
